﻿

CREATE PROCEDURE [dbo].[balcon_ImportBalansW]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@CodUnitate smallint,
	@CodNod bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)
    DECLARE @strCodNod varchar(20)
	
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveArt]') AND type in (N'U'))
		DROP TABLE [dbo].[SaveArt]

	BEGIN TRAN
	
	SET @strCodUnitate = cast(@CodUnitate as varchar(10))
	SET @strCodNod = cast(@CodNod as varchar(20))
   	
    SET @Comanda = 'DELETE FROM [ConturiSpeciale] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [ConturiCoresp] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [Rulaje] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	

	
	--Salveaza articolele si legatura cu conturile
	SET @Comanda = 'SELECT [IDArticol],[Cont],[ContCheltuiala],[ContVenit] INTO [SaveArt] FROM [Articole]  WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=''N/A'',[ContCheltuiala]=''N/A'',[ContVenit]=''N/A'' WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'DELETE FROM [Conturi] WHERE [CodUnitate] =' + @strCodUnitate  + ' AND [Cont]<>''N/A'''
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Sterge Conturi - ok')

	SET @Comanda = 'INSERT INTO [Conturi]
           ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
		SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [Cont],LTRIM(RTRIM([Denumire])) [Denumire],[Functie],[Terminal],'+
		'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [ContParinte],'+
		'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
	IF @ServerName <> ''
	BEGIN	
		SET @Comanda = @Comanda + '[' + @ServerName+'].'
	END	
	SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''S'' AND CodNod='  + @strCodNod   
    PRINT(@Comanda)
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

	DECLARE @index smallint
	SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [Conturi]
			   ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
			SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),LTRIM(RTRIM([Denumire])),[Functie],[Terminal],'+
			'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),'+
			'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''A'+CAST(@index AS varchar(1))+''' AND CodNod='  + @strCodNod   
	    
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1	
	END
	
	--,[ContCheltuiala]=a.[ContCheltuiala],[ContVenit]=a.[ContVenit]
	PRINT('Import Conturi - ok')	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=a.[Cont] FROM [SaveArt] a  WHERE a.IDArticol = Articole.IDarticol ' +
	'AND [CodUnitate] =' + @strCodUnitate  + ' AND EXISTS(SELECT * FROM [Conturi] b WHERE a.[Cont]=b.[Cont] AND [CodUnitate] =' + @strCodUnitate+' )'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	
	PRINT('Import Conturi Articole- ok')

    SET @Comanda = 'UPDATE [Conturi] SET [EsteAnalitic]=0 WHERE LEN(LTRIM(RTRIM([Cont])))<=4'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    
    SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [ArticoleAliniate]
			   ([ArtAl],[Denumire],[AAParinte],[Terminal],[Titlu],[Categorii],[TermDenumire],[StartDenumire],[Anexa6],[CodUnitate])
		SELECT dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0),LTRIM(RTRIM(a.[Denumire])) Denumire'+
		',dbo.GetContParinte([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0) [AAParinte],[Terminal] ,e.Denumire [Titlu],'
		
		IF @index=1
			SET @Comanda = @Comanda + '''[CLT]'''
		ELSE	
			SET @Comanda = @Comanda + '''[TOT]'''
		SET @Comanda = @Comanda +  ' [Categorii],aduna [TermDenumire],'''' [StartDenumire], 0 [Anexa6],'+@strCodUnitate+'  FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBCoduri] a JOIN '
		
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBTitluri] e ON a.CodTitlu = e.CodTitlu WHERE ' +
		 ' NOT EXISTS(SELECT * FROM [ArticoleAliniate] b WHERE dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0)=b.[ArtAl] AND [CodUnitate] =' + @strCodUnitate+' )' +
		 ' AND Tip=''A'+CAST(@index AS varchar(1))+''' AND Analitic1<>0'
		PRINT(@Comanda) 
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1		
	END
	
	
	PRINT('ArticoleAliniarte - ok')
    
    /*SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] ON;INSERT INTO [Parteneri]
           ([IDPartener],[Denumire],[CodFiscal],[IDBanca],[ContBancar],[Cont],[CodUnitate],[CUI])
		SELECT		[IDFurnizor],[Denumire],[CodFiscal],0,[ContBancar],[Cont],'+@strCodUnitate+',[IDFurnizor] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Furnizori] '
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Parteneri - ok')

    SET @Comanda = 'INSERT INTO [ConturiSpeciale]
           ([Cont],[Tipuri],[Descriere],[CodUnitate])
			SELECT [Cont],[Tipuri],[Descriere],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiSpeciale]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('ConturiSpeciale - ok')
	
    SET @Comanda = 'DELETE FROM [Jurnale] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	*/

    /*SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] ON;INSERT INTO [Jurnale]
           ([IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],[CodUnitate])
SELECT [IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Jurnale] WHERE [IDJurnal]<>[IdParinte]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Jurnale - ok')

    SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] ON;INSERT INTO [Ordonantari]
           ([IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,[ArtAl]
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],[CodUnitate])
SELECT [IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,SUBSTRING(REPLACE([Cont],''7701.'',''''),1,40)
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Ordonantari]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Ordonantari - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	



    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] ON;INSERT INTO [RulajeDoc]
           ([IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],[CodUnitate])
SELECT [IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RulajeDoc]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('RulajeDoc - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] ON;INSERT INTO [Rulaje]
           ([CodFormula]
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],[CodUnitate])
SELECT [CodFormula]*1000
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Rulaje]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Rulaje - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE NOT EXISTS(SELECT * FROM [Rulaje] a 
 WHERE a.IdDocument=[RulajeDoc].IDDocument)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
*/
 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END